package br.ufrj.neg.evento.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import br.ufrj.neg.evento.bean.LogomarcaBean;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.preparacao.bean.NegociacaoBean;


public class LogomarcaModel {

	public static int cadastraLogomarca(LogomarcaBean lb){
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			conn.setAutoCommit(false);

			String sql = "INSERT INTO logomarca(logo_cd_id,nego_cd_id,logo_ds_url,logo_cd_tipo ) VALUES" +
					"(DEFAULT,?, ?, ?) RETURNING logo_cd_id";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,lb.getIdNegociacao());
			ps.setString(2, lb.getUrl() );
			ps.setInt(3, lb.getTipo());
			
			rs = ps.executeQuery();
			rs.next();
			int idRetorno = rs.getInt(1);
			
			conn.commit();
			conn.close();
			ps.close();
			
			return idRetorno;
			
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
		
	}
	
	public static int cadastraLogomarcasPorNegociacao(NegociacaoBean neg, LogomarcaBean logoPropria, LogomarcaBean logoCliente) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			conn.setAutoCommit(false);

			String sql = "INSERT INTO logomarca (logo_cd_id,nego_cd_id,logo_ds_url,logo_cd_tipo ) VALUES" +
					"(DEFAULT, ?, ?, ?)," +
					"(DEFAULT, ?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,neg.getIdNegociacao());
			ps.setString(2, logoPropria.getUrl() );
			ps.setInt(3, logoPropria.getTipo());
			ps.setInt(4,neg.getIdNegociacao() );
			ps.setString(5, logoCliente.getUrl());
			ps.setInt(6, logoCliente.getTipo());
			
			ps.executeUpdate();
			
			
			conn.commit();
			conn.close();
			ps.close();
			return 1;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 
	 * @param Integer com o id da negocicao que quero pegar as urls
	 * @return Lista com as logomarcas daquela negociacao.
	 */
	public static HashMap<Integer, LogomarcaBean> getLogomarcasPorNegociacao(Integer idNegocio) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		HashMap<Integer, LogomarcaBean> logomarcas = new HashMap<Integer,LogomarcaBean>();
		
		LogomarcaBean logomarca = null;

		try {
			conn.setAutoCommit(false);

			String sql = "select * from logomarca where nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, idNegocio);
			ResultSet rs = ps.executeQuery();
		
			
			while (rs.next()) {
			
				logomarca = new LogomarcaBean(rs.getString("logo_ds_url"),
						rs.getInt("logo_cd_tipo"),rs.getInt("nego_cd_id"),rs.getInt("logo_cd_id"));

				logomarcas.put(logomarca.getTipo(),logomarca);
				
			}
			rs.close();
			ps.close();
			conn.close();

			return logomarcas;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;

	}
	

	public static boolean removeLogomarca(LogomarcaBean lb) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
						
			
			String sql = "DELETE FROM logomarca WHERE logo_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, lb.getId());
			ps.executeUpdate();
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public static boolean removeLogomarcaPorNegociacao(NegociacaoBean neg) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "DELETE FROM logomarca WHERE nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, neg.getIdNegociacao());
			ps.executeUpdate();
						
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public static boolean removeLogomarcaPorNegociacaoTipo(NegociacaoBean neg,Integer tipoLogomarca) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "DELETE FROM logomarca WHERE nego_cd_id = ? AND logo_cd_tipo = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, neg.getIdNegociacao());
			ps.setInt(2, tipoLogomarca);
			ps.executeUpdate();
						
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	public static boolean atualizaLogomarcaPorNegociacaoETipo(NegociacaoBean neg,LogomarcaBean lb) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "UPDATE logomarca SET logo_ds_url=? WHERE nego_cd_id = ? AND logo_cd_tipo = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, lb.getUrl());
			ps.setInt(2, neg.getIdNegociacao());
			ps.setInt(3, lb.getTipo());
			ps.executeUpdate();
						
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public static boolean atualizaLogomarcasPorNegociacao(NegociacaoBean neg, LogomarcaBean logoNegociador, LogomarcaBean logoCliente) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			
			//Ideia era usar um update só, mas não consegui.
			
			
			String sql = "UPDATE logomarca SET logo_ds_url = ? WHERE nego_cd_id = ? AND logo_cd_tipo = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(3, logoNegociador.getTipo());
			ps.setString(1, logoNegociador.getUrl());
			ps.setInt(2, neg.getIdNegociacao());
			ps.executeQuery();
			
			ps = conn.prepareStatement(sql);
			ps.setInt(3, logoCliente.getTipo());
			ps.setString(1, logoCliente.getUrl());				
			ps.setInt(2, neg.getIdNegociacao());
			ps.executeQuery();
						
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}



}
